<?php

function sqlSearch( $sql, $tag, $pos = 0 )
{
    $state = 'A';
    $len = strlen( $sql );
    $string = '';
    $current = $pos;
    $tag = strtolower( $tag );
    $num_parenthesis = 0;

    while ( $current < $len ) {
        switch ( $state ) {

            case 'A':
                if ( $sql[ $current ] == '`' ) {
                    $state = 'B';
                    $string = '';
                } else if ( $sql[ $current ] == '\'' ) {
                    $state = 'C';
                    $string = '';
                } else if ( $sql[ $current ] == '"' ) {
                    $state = 'D';
                    $string = '';
                } else if ( $sql[ $current ] == '(' ) {
                    $state = 'E';
                    $string = '';
                    $num_parenthesis++;
                }
                else $string.=$sql[ $current ];

                if ( strrpos( strtolower( $string ), $tag ) > -1 )
                        return ( $current - strlen( $tag ) + 1 );

                break;

            case 'B':
                if ( $sql[ $current ] == '`' ) $state = 'A';
                break;

            case 'C':
                if ( $sql[ $current ] == '\'' ) $state = 'A';
                break;

            case 'D':
                if ( $sql[ $current ] == '"' ) $state = 'A';
                break;

            case 'E':
                if ( $sql[ $current ] == ')' ) $num_parenthesis--;

                else if ( $sql[ $current ] == '(' ) $num_parenthesis++;

                if ( $num_parenthesis == 0 ) $state = 'A';

                break;
        }

        $current++;
    }

    return false;
}

function sqlSplit( $sql, $tag )
{
    $state = 'A';
    $len = strlen( $sql );
    $tag_len = -1 * strlen( $tag );
    $current = 0;
    // $tag = strtolower($tag);
    $num_parenthesis = 0;
    $chunks = array( '' );
    $current_chunk = 0;

    while ( $current < $len ) {
        switch ( $state ) {

            case 'A':
                if ( $sql[ $current ] == '`' ) {
                    $state = 'B';
                } else if ( $sql[ $current ] == '\'' ) {
                    $state = 'C';
                } else if ( $sql[ $current ] == '"' ) {
                    $state = 'D';
                } else if ( $sql[ $current ] == '(' ) {
                    $state = 'E';
                    $num_parenthesis++;
                }

                $chunks[ $current_chunk ].=$sql[ $current ];

                if ( substr( $chunks[ $current_chunk ], $tag_len ) == $tag ) {
                    $chunks[ $current_chunk ] = substr( $chunks[ $current_chunk ],
                            0, $tag_len );
                    $current_chunk++;
                    $chunks[ $current_chunk ] = '';
                }



                break;

            case 'B':
                if ( $sql[ $current ] == '`' ) $state = 'A';

                $chunks[ $current_chunk ].=$sql[ $current ];
                break;

            case 'C':
                if ( $sql[ $current ] == '\'' ) $state = 'A';

                $chunks[ $current_chunk ].=$sql[ $current ];
                break;

            case 'D':
                if ( $sql[ $current ] == '"' ) $state = 'A';

                $chunks[ $current_chunk ].=$sql[ $current ];
                break;

            case 'E':
                if ( $sql[ $current ] == ')' ) $num_parenthesis--;

                else if ( $sql[ $current ] == '(' ) $num_parenthesis++;

                if ( $num_parenthesis == 0 ) $state = 'A';

                $chunks[ $current_chunk ].=$sql[ $current ];
                break;
        }

        $current++;
    }

    return $chunks;
}

function stripDoubleBlanks( $sql )
{
    $state = 'A';
    $len = strlen( $sql );
    $string = '';
    $current = 0;

    while ( $current < $len ) {
        switch ( $state ) {

            case 'A':
                if ( $sql[ $current ] == '`' ) $state = 'B';

                else if ( $sql[ $current ] == '\'' ) $state = 'C';

                else if ( $sql[ $current ] == '"' ) $state = 'D';

                else if ( $sql[ $current ] == ' ' ) $state = 'E';


                $string.=$sql[ $current ];
                break;

            case 'B':
                if ( $sql[ $current ] == '`' ) $state = 'A';

                $string.=$sql[ $current ];
                break;

            case 'C':
                if ( $sql[ $current ] == '\'' ) $state = 'A';

                $string.=$sql[ $current ];
                break;

            case 'D':
                if ( $sql[ $current ] == '"' ) $state = 'A';

                $string.=$sql[ $current ];
                break;

            case 'E':

                if ( $sql[ $current ] != ' ' ) {

                    $string.=$sql[ $current ];

                    if ( $sql[ $current ] == '`' ) $state = 'B';

                    else if ( $sql[ $current ] == '\'' ) $state = 'C';

                    else if ( $sql[ $current ] == '"' ) $state = 'D';
                    else $state = 'A';
                }

                break;
        }

        $current++;
    }

    return trim( $string );
}

function exploreSELECT( $sql )
{ //@TODO NO ESTA PREPARADO TODAVÍA PARA SENTENCIAS UNION
    $sql = str_replace( array( '\r', '\n' ), array( ' ', ' ' ), $sql );
    $sql = stripDoubleBlanks( $sql );

    if ( sqlSearch( $sql, 'SELECT' ) !== 0 ) return;

    $struct = array(
        'reserved' => '',
        'from' => '',
        'where' => '',
        'end' => '',
        'fields' => array( ),
        'tables' => array( )
    );

    //antes de nada debería dividir por el UNION y tratarlas como dos consultas

    $reserveds = array( 'ALL', 'DISTINCT', 'DISTINCTROW', 'HIGH_PRIORITY', 'STRAIGHT_JOIN',
        'SQL_SMALL_RESULT', 'SQL_BIG_RESULT', 'SQL_BUFFER_RESULT', 'SQL_CACHE', 'SQL_NO_CACHE',
        'SQL_CALC_FOUND_ROWS' );

    $posfrom = sqlSearch( $sql, 'FROM' );
    if ( $posfrom == false ) return false;

    $poswhere = sqlSearch( $sql, 'WHERE' );
    $posgroup = sqlSearch( $sql, 'GROUP BY' );

    $select = substr( $sql, 7, $posfrom - 7 - 1 ); //Quito espacios de inicio y fin

    if ( $poswhere !== false ) {
        $struct[ 'from' ] = substr( $sql, $posfrom + 5,
                $poswhere - $posfrom - 5 - 1 );

        if ( $posgroup !== false ) {
            $struct[ 'where' ] = substr( $sql, $poswhere + 6,
                    $posgroup - $poswhere - 6 - 1 );
            $struct[ 'end' ] = substr( $sql, $posgroup );
        }
        else $struct[ 'where' ] = substr( $sql, $poswhere + 6 );
    }
    else {
        if ( $posgroup !== false ) {
            $struct[ 'from' ] = substr( $sql, $posfrom + 5,
                    $posgroup - $posfrom - 5 - 1 );
            $struct[ 'end' ] = substr( $sql, $posgroup );
        }
        else $struct[ 'from' ] = substr( $sql, $posfrom + 5 );
    }


    //AHORA TENGO QUE VER SI TENGO ALGUNA PALABRA RESERVADA ANTES DE LOS CAMPOS
    //tengo que buscar la primera palabra, si es distinta de alguna de las reservadas significaría que es un campo
    //extraemos las palabras reservadas del select

    $posespacio = sqlSearch( $select, ' ' );

    while ( $posespacio !== false ) {

        if ( !in_array( strtoupper( substr( $select, 0, $posespacio - 1 ) ),
                        $reserveds ) ) break;

        else {
            $struct[ 'reserved' ].=' ' . substr( $select, 0, $posespacio - 1 );
            $select = substr( $select, $posespacio + 1 );
            $posespacio = sqlSearch( $select, ' ' );
        }
    }

    //ahora en el SELECT tienen que estar todos los campos
    //una vez supero las reservadas las demás son campos

    $chunks = sqlSplit( $select, ',' );

    //ahora tengo que ver las posibles opciones que hay
    //OJO, aqui faltan las funciones que si están dentro de parentesis no se ven afectadas
    $pattern = '/^\s*((`([^`]+)`|[a-z_][a-z0-9_]*)\.)?(`([^`]+)`|[a-z_][a-z0-9_]*)(\sas\s`([^`]+)`|\sas\s([a-z_][a-z0-9_]*))?\s*$/i';
    $pattern_as = '/\s+as\s(`([^`]+)`|[a-z_][a-z0-9_]*)\s*$/i';

    foreach ( $chunks as $c ) {

        $matches = array( );

        if ( preg_match( $pattern, $c, $matches ) ) {

            $table = '';
            //la tabla o $matches[4] o $matches[5]; o vacio
            if ( !empty( $matches[ 3 ] ) ) $table = $matches[ 3 ];
            else if ( !empty( $matches[ 2 ] ) ) $table = $matches[ 2 ];

            //el campo o $matches[8] o $matches[9];
            if ( !empty( $matches[ 5 ] ) ) $field = $matches[ 5 ];
            else// if(!empty($matches[8]))
                $field = $matches[ 4 ];

            //el alias o $matches[13] o $matches[14]; o vacio
            if ( !empty( $matches[ 7 ] ) ) $alias = $matches[ 7 ];
            else if ( !empty( $matches[ 8 ] ) ) $alias = $matches[ 8 ];
            else $alias = $field;


            $struct[ 'fields' ][ $alias ] = array( 1, $table, $field );
        }
        else if ( preg_match( $pattern_as, $c, $matches ) ) { //si es una funcion deberá tener as!!!!
            if ( empty( $matches[ 2 ] ) ) $alias = $matches[ 1 ];
            else if ( !empty( $matches[ 4 ] ) ) $table = $matches[ 4 ];

            $func = substr( $c, 0, -1 * strlen( $matches[ 0 ] ) );
            $struct[ 'fields' ][ $alias ] = array( 0, '', $func );
        }
        else {
            echo $c . "\n" . "<BR>";
        }
        //en otro caso es una funcion a la que debemos extraer el as
    }


    //ahora extraigo todas las tablas del from
    $from = ',' . $struct[ 'from' ];
    $pattern = '/(join\s|,\s?)((`([^`]+)`|[a-z_][a-z0-9_]*)\.)?(`([^`]+)`|[a-z_][a-z_0-9]*)(\sas\s`([^`]+)`|\sas\s([a-z_][a-z0-9_]*))?/i';
    $matches = array( );

    if ( preg_match_all( $pattern, $from, $matches ) ) {
        $num_tables = count( $matches[ 0 ] );

        for ( $i = 0; $i < $num_tables; $i++ ) {

            $db = '';

            if ( !empty( $matches[ 3 ][ $i ] ) ) $db = $matches[ 3 ][ $i ];
            else if ( !empty( $matches[ 2 ][ $i ] ) ) $db = $matches[ 2 ][ $i ];

            if ( empty( $matches[ 6 ][ $i ] ) ) $table = $matches[ 5 ][ $i ];
            else $table = $matches[ 6 ][ $i ];

            if ( !empty( $matches[ 8 ][ $i ] ) ) $alias = $matches[ 8 ][ $i ];
            else if ( !empty( $matches[ 9 ][ $i ] ) )
                    $alias = $matches[ 9 ][ $i ];
            else $alias = $table;

            $struct[ 'tables' ][ $alias ] = array( $db, $table );
        }
    }

    return $struct;
}

error_reporting( E_ALL | E_STRICT );

$coincidencias = exploreSELECT( "SELECT m.id as id, title, description, project, main_milestone, owner, CONCAT_WS(',',CONCAT_WS(' ',first_surname, second_surname), name) as owner_name, DATE_FORMAT(when,'%d-%m-%Y %T') as creada, closed, DATE_FORMAT(when_closed,'%d-%m-%Y %T') as cerrada, DATE_FORMAT(updated,'%d-%m-%Y %T') as actualizada, num_childs, num_encloseds, votes_plus, votes_minus, resource_project, resource_milestone
            FROM milestones as m INNER JOIN users as u ON owner=u.id" );

var_dump( $coincidencias );
?>
